#!/usr/bin/python3

# 需要安装的python库
# pip install openpyxl
# pip install pandas

import html
import json
import os
import re

import pandas as pd

RESET = "\x1B[0m"
RED = "\x1B[31m"
GREEN = "\x1B[32m"
YELLOW = "\x1B[33m"


def get_files_list():
    files_list = []
    os.makedirs('json', exist_ok=True)
    for root, dirs, files in os.walk('json'):
        for file in files:
            if file.endswith('.json') or file.endswith('.txt'):
                files_list.append(os.path.join(root, file))

    if len(files_list) == 0:
        print(f"{RED}error：{RESET}未找到json或txt文件")
        exit(1)
    return files_list


def load_json(file_path):
    try:
        with open(file_path, 'r', encoding='utf-8') as f:
            return json.loads(f.read())
    except:
        print(f"{RED}error{RESET}：无法打开 {os.path.basename(file_path)} 文件")


def get_QuestionAnswer_text(content):
    error_tip = "获取内容失败"
    if content is not None and "</span>" in content:
        text = re.match(r'<p><span .*">(.*?)<', content)
        if text is not None:
            text = text.group(1)
            text = html.unescape(text)
    elif content is not None and "<p>" in content:
        text = re.match(r'^<p>(.*?)<', content)
        if text is not None:
            text = text.group(1)
            text = html.unescape(text)
    else:
        text = content

    if content is not None and (text is None or len(text) == 0):
        text = error_tip
    return text.strip() if text is not None else text


def get_test_name(json_data):
    CourseName = json_data["Result"][0]["CourseName"]
    if "通信原理实验 课堂测验-" in CourseName:
        test_name = CourseName[12:]
    else:
        test_name = json_data["Result"][0]["CourseExperName"]
    return test_name


def add_data2excel(json_data, xlsx_file):
    CourseName_dict = {"数字基带信号实验": "实验1", "时域均衡实验": "实验2", "2FSK调制解调实验": "实验3",
                       "2PSK调制解调实验": "实验4", "PCM模拟信号数字化实验": "实验5", "载波时钟提取实验": "实验6",
                       "频带传输系统 综合实验": "实验7", "综合测试1综合测验1": "综合1", "综合测试2综合测验2": "综合2",
                       "综合测试3综合测验3": "综合3", "error": "未知"}
    get_CourseNum_key = lambda CourseNum_key, dict: next((key for key in dict.keys() if CourseNum_key in key), "error")
    total = 0
    Answer_key = {'A': 'OptionA', 'B': 'OptionB', 'C': 'OptionC', 'D': 'OptionD'}
    Answer_content = {}
    CourseNum_key = get_CourseNum_key(get_test_name(json_data), CourseName_dict)
    for item in json_data['Result']:
        question_content = get_QuestionAnswer_text(item['Question'])
        for key in ['A', 'B', 'C', 'D']:
            Answer_content[key] = get_QuestionAnswer_text(item[Answer_key[key]])
        new_data = pd.DataFrame(
            {'实验名称': [CourseName_dict[CourseNum_key]], '题型': [item['AnswerTypeEnumDesc']],
             '问题': [question_content], '答案': [item['Answer']], '选项A': [Answer_content["A"]],
             '选项B': [Answer_content["B"]], '选项C': [Answer_content["C"]], '选项D': [Answer_content["D"]]})
        merged_data = pd.merge(xlsx_file, new_data, how='inner')
        if merged_data.empty:
            xlsx_file = pd.concat([xlsx_file, new_data], ignore_index=True)
            if question_content == "获取内容失败":
                question_content = f"{RED}{question_content}{RESET}"
            print(f"{GREEN}添加数据{RESET} {CourseName_dict[CourseNum_key]} {item['AnswerTypeEnumDesc']} {question_content}")
            total += 1
    return total, xlsx_file


def export_json2xlsx():
    total = 0
    xlsx_file = pd.read_excel("通原实验.xlsx")
    for json_path in get_files_list():
        total_num, xlsx_file = add_data2excel(load_json(json_path), xlsx_file)
        total += total_num
    xlsx_file.to_excel("通原实验.xlsx", index=False)
    print(f"\n{GREEN}完成，添加 {total} 项数据{RESET}")


def check_excel():
    if not os.path.exists("通原实验.xlsx"):
        first_row_data = {'实验名称': [], '题型': [], '问题': [], '答案': [], '选项A': [], '选项B': [], '选项C': [],
                          '选项D': []}
        df = pd.DataFrame(first_row_data)
        df.to_excel("通原实验.xlsx", index=False)


def sort_excel():
    xlsx_file = pd.read_excel("通原实验.xlsx")
    custom_order = ['单选题', '多选题', '判断题']
    CourseExperName = ['实验1', '实验2', '实验3', '实验4', '实验5', '实验6', '实验7', '综合1', '综合2', '综合3', '未知']
    xlsx_file['题型'] = pd.Categorical(xlsx_file['题型'], categories=custom_order, ordered=True)
    xlsx_file['实验名称'] = pd.Categorical(xlsx_file['实验名称'], categories=CourseExperName, ordered=True)
    xlsx_file = xlsx_file.sort_values(by=['实验名称', '题型', '问题', '答案'])
    xlsx_file.to_excel("通原实验.xlsx", index=False)


def main():
    try:
        check_excel()
        export_json2xlsx()
        sort_excel()
    except PermissionError:
        print(f"{RED}出现错误 PermissionError{RESET}")


if __name__ == "__main__":
    main()
